USE [dbmigration]
GO
/****** Object:  StoredProcedure [dbo].[sp_record202]    Script Date: 09/03/2013 10:05:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Ubaid Raja>
-- Create date: <September 02, 2013 7:58PM>
-- Description:	<To fetch data for the record 202 from the existing database.>
-- =============================================
ALTER PROCEDURE [dbo].[sp_record202]
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
-- Get process id from the tx_reference_values table

DECLARE @Output Integer;
set @Output = (SELECT column_value FROM tx_reference_values where record_type = 'tx_process_info' and column_name = 'process_id');
--print @Output;

INSERT INTO [dbmigration].[dbo].[tx_202EMPLPIM](
finalized,
process_id,
ssn,
empl_id,
user_id,
fn,mn,ln,
suffix,
verdiv,
mrhdate,
eestattype,
orighiredate,
termdate,
adjhiredate,
payfreq,
l1_1,l2_1,
city_1,state_1,zip_1,
home_phone,work_phone,fax,other_phone,
email_1,email_2,
marital_status,gender,birth_date)

SELECT 0,@Output, 
SUBSTRING(ssn,0,12),
SUBSTRING(EmpNo,0,65),
SUBSTRING(ssn,0,65),
SUBSTRING(FirstName,0,65),
SUBSTRING(MidName,0,65),
SUBSTRING(LastName,0,65),
SUBSTRING(Suffix,0,13),
SUBSTRING(Dept,0,13),
CONVERT(VARCHAR(8),HireDate , 112) AS [YYYYMMDD] ,
SUBSTRING(Status,0,2),
CONVERT(VARCHAR(8),OrigHireDate , 112) AS [YYYYMMDD] , 
CONVERT(VARCHAR(8),TerminationDate , 112) AS [YYYYMMDD], 
CONVERT(VARCHAR(8),AdjHireDate , 112) AS [YYYYMMDD] ,
PayFreq, 
CASE ADDRESS1 WHEN '' THEN 'NA' ELSE SUBSTRING(ADDRESS1,0,61) end, 
CASE ADDRESS2 WHEN '' THEN 'NA' ELSE SUBSTRING(ADDRESS2,0,61) end,
CASE City WHEN '' THEN 'NA' ELSE SUBSTRING(City,0,61) end,
SUBSTRING(State,0,3),
CASE Zip WHEN '' THEN 'NA' ELSE SUBSTRING(Zip,0,17) end , 
CASE HomePhone WHEN '' THEN 'NA' ELSE SUBSTRING(HomePhone,0,17) end ,
CASE WorkPhone WHEN '' THEN 'NA' ELSE SUBSTRING(WorkPhone,0,17) end , 
CASE FaxPhone WHEN '' THEN 'NA' ELSE SUBSTRING(FaxPhone,0,17) end , 
CASE OtherPhone WHEN '' THEN 'NA' ELSE SUBSTRING(OtherPhone,0,17) end ,
SUBSTRING(EmailAddress1,0,257),
SUBSTRING(EmailAddress2,0,257),
MaritalState,
Sex,
CONVERT(VARCHAR(8),BirthDate , 112) AS [YYYYMMDD]  
FROM [disys-dev01].[PeopleSoft].[dbo].EMP_MAIN;

--SELECT 0,@Output, SUBSTRING(ssn,0,11),EmpNo,SUBSTRING(ssn,0,11),FirstName,MidName,LastName,Suffix,Dept,CONVERT(VARCHAR(8),HireDate , 112) AS [YYYYMMDD] ,Status,CONVERT(VARCHAR(8),OrigHireDate , 112) AS [YYYYMMDD] , CONVERT(VARCHAR(8),TerminationDate , 112) AS [YYYYMMDD], CONVERT(VARCHAR(8),AdjHireDate , 112) AS [YYYYMMDD] ,PayFreq, CASE ADDRESS1 WHEN '' THEN 'NA' ELSE SUBSTRING(ADDRESS1,0,59) end, CASE ADDRESS2 WHEN '' THEN 'NA' ELSE SUBSTRING(ADDRESS2,0,59) end,CASE City WHEN '' THEN 'NA' ELSE SUBSTRING(City,0,59) end,State,CASE Zip WHEN '' THEN 'NA' ELSE SUBSTRING(Zip,0,15) end , CASE HomePhone WHEN '' THEN 'NA' ELSE SUBSTRING(HomePhone,0,15) end ,CASE WorkPhone WHEN '' THEN 'NA' ELSE SUBSTRING(WorkPhone,0,15) end , CASE FaxPhone WHEN '' THEN 'NA' ELSE SUBSTRING(FaxPhone,0,15) end , CASE OtherPhone WHEN '' THEN 'NA' ELSE SUBSTRING(OtherPhone,0,15) end ,EmailAddress1,EmailAddress2,MaritalState,Sex,CONVERT(VARCHAR(8),BirthDate , 112) AS [YYYYMMDD]  FROM [disys-dev01].[PeopleSoft].[dbo].EMP_MAIN;


DECLARE @column_name varchar(200)
DECLARE @column_value varchar(200)
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @getRecordRef CURSOR
SET @getRecordRef = CURSOR FOR
SELECT column_name,column_value
FROM tx_reference_values
where record_type = 'tx_202emplpim'
OPEN @getRecordRef
FETCH NEXT
FROM @getRecordRef INTO @column_name,@column_value
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLQuery = 'update [tx_202EMPLPIM] set '+ @column_name +'='''+ @column_value+''' where finalized=0'
EXECUTE(@SQLQuery)
FETCH NEXT
FROM @getRecordRef INTO @column_name,@column_value
END
CLOSE @getRecordRef
DEALLOCATE @getRecordRef
END